import pymysql
from matplotlib import pyplot as plt
from matplotlib import font_manager as fm, rcParams
import numpy as np
import jieba
from wordcloud import wordcloud
from matplotlib import colors

rcParams['font.sans-serif'] = ['SimHei']
rcParams['axes.unicode_minus'] = False


def one():
    conn = pymysql.connect(host='127.0.0.1', user='root', password='root', port=3306, db='python', charset='utf8')
    cur = conn.cursor()
    sql = 'select type,count(*) from last group by type'
    cur.execute(sql)
    name = []
    cnt = []
    datas = cur.fetchall()
    for d in datas:
        name.append(d[0])
        cnt.append(d[1])
    plt.figure(figsize=(7, 4), dpi=80)
    plt.pie(cnt, labels=name, autopct='%3.3f%%', shadow=True, startangle=60)
    plt.savefig('pic/one.png')


def two():
    rcParams['font.sans-serif'] = ['SimHei']
    rcParams['axes.unicode_minus'] = False
    conn = pymysql.connect(host='127.0.0.1', user='root', password='root', port=3306, db='python', charset='utf8')
    cur = conn.cursor()
    sql = 'select floor(bfl/10000),pls from last where bfl/10000 <= 1000 and type="华语"'
    cur.execute(sql)
    bflw = []
    plsg = []
    datas = cur.fetchall()
    for d in datas:
        bflw.append(d[0])
        plsg.append(d[1])
    z1 = np.polyfit(bflw, plsg, 1)
    p1 = np.poly1d(z1)
    yvals = p1(bflw)
    plt.figure(figsize=(7, 4), dpi=80)
    plt.scatter(bflw, plsg, s=1.4)
    plt.plot(bflw, yvals)
    sql = 'select floor(bfl/10000),pls from last where bfl/10000 <= 1000 and type="欧美"'
    cur.execute(sql)
    bflw = []
    plsg = []
    datas = cur.fetchall()
    for d in datas:
        bflw.append(d[0])
        plsg.append(d[1])
    z1 = np.polyfit(bflw, plsg, 1)
    p1 = np.poly1d(z1)
    yvals = p1(bflw)
    plt.scatter(bflw, plsg, s=1.4)
    plt.plot(bflw, yvals)
    sql = 'select floor(bfl/10000),pls from last where bfl/10000 <= 1000 and type="日语"'
    cur.execute(sql)
    bflw = []
    plsg = []
    datas = cur.fetchall()
    for d in datas:
        bflw.append(d[0])
        plsg.append(d[1])
    z1 = np.polyfit(bflw, plsg, 1)
    p1 = np.poly1d(z1)
    yvals = p1(bflw)
    plt.scatter(bflw, plsg, s=1.4)
    plt.plot(bflw, yvals)
    sql = 'select floor(bfl/10000),pls from last where bfl/10000 <= 1000 and type="韩语"'
    cur.execute(sql)
    bflw = []
    plsg = []
    datas = cur.fetchall()
    for d in datas:
        bflw.append(d[0])
        plsg.append(d[1])
    z1 = np.polyfit(bflw, plsg, 1)
    p1 = np.poly1d(z1)
    yvals = p1(bflw)
    plt.scatter(bflw, plsg, s=1.4)
    plt.plot(bflw, yvals)
    sql = 'select floor(bfl/10000),pls from last where bfl/10000 <= 1000 and type="粤语"'
    cur.execute(sql)
    bflw = []
    plsg = []
    datas = cur.fetchall()
    for d in datas:
        bflw.append(d[0])
        plsg.append(d[1])
    z1 = np.polyfit(bflw, plsg, 1)
    p1 = np.poly1d(z1)
    yvals = p1(bflw)
    plt.scatter(bflw, plsg, s=1.4)
    plt.plot(bflw, yvals)
    plt.savefig('pic/two.png')


def three():
    rcParams['font.sans-serif'] = ['SimHei']
    rcParams['axes.unicode_minus'] = False
    conn = pymysql.connect(host='127.0.0.1', user='root', password='root', port=3306, db='python', charset='utf8')
    cur = conn.cursor()
    sql = 'select scs from last where type="华语"'
    cur.execute(sql)
    rs1 = []
    datas = cur.fetchall()
    for d in datas:
        rs1.append(d[0])
    rs1 = np.log2(rs1)
    sql = 'select scs from last where type="欧美"'
    cur.execute(sql)
    rs2 = []
    datas = cur.fetchall()
    for d in datas:
        rs2.append(d[0])
    rs2 = np.log2(rs2)
    sql = 'select scs from last where type="日语"'
    cur.execute(sql)
    rs3 = []
    datas = cur.fetchall()
    for d in datas:
        rs3.append(d[0])
    rs3 = np.log2(rs3)
    sql = 'select scs from last where type="韩语"'
    cur.execute(sql)
    rs4 = []
    datas = cur.fetchall()
    for d in datas:
        rs4.append(d[0])
    rs4 = np.log2(rs4)
    sql = 'select scs from last where type="粤语"'
    cur.execute(sql)
    rs5 = []
    datas = cur.fetchall()
    for d in datas:
        rs5.append(d[0])
    rs5 = np.log2(rs5)
    plt.figure(figsize=(7, 4), dpi=80)
    label = ['华语', '欧美', '日语', '韩语', '粤语']
    plt.boxplot([rs1, rs2, rs3, rs4, rs5], labels=label)
    plt.savefig('pic/three.png')


def wc():
    conn = pymysql.connect(host='127.0.0.1', user='root', password='root', port=3306, db='python', charset='utf8')
    file = open(r'E:/pyprogram/2020pylearn/last/pic/name.txt', 'w')
    cur = conn.cursor()
    sql = 'select gdm from last'
    cur.execute(sql)
    datas = cur.fetchall()
    for d in datas:
        try:
            file.write(d[0])
            file.write('\n')
        except:
            pass
    file = open(r'E:/pyprogram/2020pylearn/last/pic/name.txt', 'r')
    file.seek(0,0)
    text = file.read()
    print(text)
    cut_text = jieba.cut(text)
    result = " ".join(cut_text)
    color_list = ['#FF0000', '#a41a1a']
    colormap = colors.ListedColormap(color_list)
    exclude = {'的', '好', '你', '和', '可以', '向', '我', '会', '是'}
    wc = wordcloud.WordCloud(
        font_path=r"c:\windows\fonts\simkai.ttf",
        background_color='white',
        width=700,
        height=400,
        max_font_size=100,
        min_font_size=10,
        mode='RGBA',
        colormap=colormap,
        stopwords=exclude
    )
    wc.generate(result)
    plt.figure(figsize=(7, 4))
    plt.imshow(wc)
    plt.axis('off')
    plt.savefig('pic/wc.png', dpi=80)